package com.example.jimuyutabletcontrol.database;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;

import com.example.jimuyutabletcontrol.MapApplication;

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

public class DBHelperSend extends SQLiteOpenHelper {

    private static DBHelperSend dbHelperSend;

    private static final String DATABASE_NAME = "mapinformation.db";

    private static final int DATABASE_VERSION = 1;

    private static final String TABLE_NAME = "map";

    public synchronized static DBHelperSend getInstance() {
        if (null == dbHelperSend) {
            dbHelperSend = new DBHelperSend(MapApplication.getContext());
        }
        return dbHelperSend;
    }

    private DBHelperSend(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String SQL_CREATE_SEND_TABLE = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME + "("
                + "IMEI VARCHAR(15) NOT NULL PRIMARY KEY, GPSINFO VARCHAR(5000), " +
                "FIXTIME VARCHAR(30) DEFAULT('无时间记录'), DATAINFO VARCHAR(10) DEFAULT('无任务数据')," +
                " _id INTEGER DEFAULT(1)" + ");";
        db.execSQL(SQL_CREATE_SEND_TABLE);
    }

    public void addInfoByIMEI(String IMEI, String INFO) {
        SQLiteDatabase db = getWritableDatabase();
        String sql = "REPLACE INTO " + TABLE_NAME + "(IMEI, GPSINFO, FIXTIME, DATAINFO) " +
                "VALUES(" + IMEI + "," + "\"" + INFO + "\"" + "," + "\"" + DateFormat(new Date()) + "\"" + "," + "\"记录任务数据点击发送\"" + ")";
        db.execSQL(sql);
    }

    public void addInfoToAll(String INFO, ArrayList<String> onlineauv) {
        SQLiteDatabase db = getWritableDatabase();
        Cursor cursor = db.rawQuery("SELECT IMEI FROM " + TABLE_NAME, null);
        while (cursor.moveToNext()) {
            String IMEI = cursor.getString(cursor.getColumnIndex("IMEI"));
            if (onlineauv.contains(IMEI)) {
                String sql = "REPLACE INTO " + TABLE_NAME + "(IMEI, GPSINFO, FIXTIME, DATAINFO) " +
                        "VALUES(" + IMEI + "," + "\"" + INFO + "\"" + "," + "\"" + DateFormat(new Date()) + "\"" + "," + "\"记录任务数据点击发送\"" + ")";
                db.execSQL(sql);
            }
        }
        cursor.close();
        db.close();
    }

    public String getGpsInfo(String auvcode) {
        return getData(auvcode, "GPSINFO");
    }

    public String getFixedInfo(String auvcode) {
        return getData(auvcode, "FIXTIME");
    }

    public String getDataInfo(String auvcode) {
        return getData(auvcode, "DATAINFO");
    }

    public String getId(String auvcode) {
        return getData(auvcode, "_id");
    }

    private String getData(String auvcode, String name) {
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursorWithOnlyImei = db.rawQuery("SELECT * FROM map WHERE IMEI = " + auvcode, null);
        if (!cursorWithOnlyImei.isFirst()) {
            cursorWithOnlyImei.moveToFirst();
        }
        int index = cursorWithOnlyImei.getColumnIndex(name);
        String data = null;
        if (index >= 0) {
            data = cursorWithOnlyImei.getString(index);
        }
        cursorWithOnlyImei.close();
        db.close();
        return data;
    }

    private String DateFormat(Date date) {
        DateFormat df = null;
        df = new SimpleDateFormat("yyyy-MM-dd-HH-mm-ss");
        if (date != null && df != null) {
            return df.format(date);
        }
        return null;
    }

    public void addIMEIsInfo(String message) {
        SQLiteDatabase db = getWritableDatabase();
        String[] contents = message.split(",");
        int length = contents.length;
        String sql = "INSERT OR IGNORE INTO " + TABLE_NAME + "(IMEI) VALUES(?)";
        SQLiteStatement stat = db.compileStatement(sql);
        db.beginTransaction();
        for (int i = 1; i < length; i++) {
            stat.bindString(1, contents[i]);
            stat.executeInsert();
        }
        db.setTransactionSuccessful();
        db.endTransaction();
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    }

    public void deleteOperation(SQLiteDatabase db) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
    }
}
